From f2943cfa56e27b457579e69be8a85b2a8815da90 Mon Sep 17 00:00:00 2001 From: "Karl O. Pinc kop@karlpinc.com" Date: Wed, 22 Oct 2025 17:59:52 +0000 Subject: [PATCH] Updated community membership rules Never in a community before birth. Never in a community other than the birth community before under study. Tidy up error messages. --- .../lib/triggers/create/biography_data.m4 | 64 +++++++++++---- db/schemas/lib/triggers/create/comm_membs.m4 | 78 ++++++++++++------- doc/src/tables/comm_membs.m4 | 20 ++++- 3 files changed, 115 insertions(+), 47 deletions(-) diff --git a/db/schemas/lib/triggers/create/biography_data.m4 b/db/schemas/lib/triggers/create/biography_data.m4 index 0dbe907..46494cb 100644 --- a/db/schemas/lib/triggers/create/biography_data.m4 +++ b/db/schemas/lib/triggers/create/biography_data.m4 @@ -322,10 +322,10 @@ CREATE OR REPLACE FUNCTION biography_data_func () check_firstborn(`NEW', `Inserting a BIOGRAPHY_DATA row where') END IF; - -- An individual may not be placed in a community before the - -- individual is under study. - IF TG_OP = 'INSERT' - OR (NEW.entrydate <> OLD.entrydate) THEN + -- An individual may not be placed in a community before their birth date. + IF TG_OP = 'UPDATE' + AND ((NEW.birthdate <> OLD.birthdate) + OR (NEW.birthcomm <> OLD.birthcomm)) THEN DECLARE commmid comm_membs.commmid%TYPE; startdate comm_membs.startdate%TYPE; @@ -334,24 +334,63 @@ CREATE OR REPLACE FUNCTION biography_data_func () INTO startdate, commmid FROM comm_membs WHERE comm_membs.animid = NEW.animid - AND comm_membs.startdate < NEW.entrydate + AND comm_membs.startdate < NEW.birthdate ORDER BY comm_membs.startdate LIMIT 1; IF FOUND THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' , DETAIL = 'An individual may not be placed in a community before' + || ' their birth date' + || ': Key (AnimID) = (' + || NEW.animid + || '), Value (AnimName) = (' + || NEW.animname + || '), Value (BirthDate) = (' + || NEW.birthdate + || '), Key (CommMID) = (' + || commmid + || '), Value (StartDate) = (' + || startdate + || ')'; + END IF; + END; + END IF; + + -- An individual may not be placed in a community, that is not their + -- birth community, before the individual is under study. + IF TG_OP = 'UPDATE' + AND ((NEW.entrydate <> OLD.entrydate) + OR (NEW.birthcomm IS DISTINCT FROM OLD.birthcomm)) THEN + DECLARE + commmid comm_membs.commmid%TYPE; + startdate comm_membs.startdate%TYPE; + BEGIN + SELECT comm_membs.startdate, comm_membs.commmid + INTO startdate, commmid + FROM comm_membs + WHERE comm_membs.animid = NEW.animid + AND comm_membs.comm IS DISTINCT FROM NEW.birthcomm + AND comm_membs.startdate < NEW.entrydate + ORDER BY comm_membs.startdate + LIMIT 1; + IF FOUND THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of BIOGRAPHY_DATA' + , DETAIL = 'An individual may not be placed in a community,' + || ' that is not their birth community, before' || ' the individual is under study' || ': Key (AnimID) = (' || NEW.animid || '), Value (AnimName) = (' || NEW.animname + || '), Value (BirthComm) = (' + || textualize(`NEW.birthcomm') || '), Value (StartDate) = (' || NEW.startdate - || ' has a related COMM_MEMBS row with ' - || ' Key (CommMID) = (' + || '), Key (CommMID) = (' || commmid - || '), and Value (StartDate) = (' + || '), Value (StartDate) = (' || startdate || ')'; END IF; @@ -360,8 +399,8 @@ CREATE OR REPLACE FUNCTION biography_data_func () -- An individual may not be placed in a community after the -- individual left the study. - IF TG_OP = 'INSERT' - OR (NEW.entrydate <> OLD.entrydate) THEN + IF TG_OP = 'UPDATE' + AND NEW.entrydate <> OLD.entrydate THEN DECLARE commmid comm_membs.commmid%TYPE; enddate comm_membs.enddate%TYPE; @@ -384,10 +423,9 @@ CREATE OR REPLACE FUNCTION biography_data_func () || NEW.animname || '), Value (DepartDate) = (' || NEW.departdate - || ' has a related COMM_MEMBS row with' - || ' Key (CommMID) = (' + || '), Key (CommMID) = (' || commmid - || ') and Value (EndDate) = (' + || ') Value (EndDate) = (' || enddate || ')'; END IF; diff --git a/db/schemas/lib/triggers/create/comm_membs.m4 b/db/schemas/lib/triggers/create/comm_membs.m4 index 0a6df56..677e69b 100644 --- a/db/schemas/lib/triggers/create/comm_membs.m4 +++ b/db/schemas/lib/triggers/create/comm_membs.m4 @@ -43,6 +43,7 @@ CREATE OR REPLACE FUNCTION comm_membs_func () -- An individual may not be recorded in more than one community on -- any given day. + -- (This is a nicer error message than a unique index would supply.) DECLARE other_row comm_membs%ROWTYPE; BEGIN @@ -128,22 +129,50 @@ CREATE OR REPLACE FUNCTION comm_membs_func () END IF; END; - -- An individual may not be placed in a community before the - -- individual is under study. + -- Date restrictions based on demographic info from BIOGRAPHY_DATA DECLARE - entrydate biography_data.entrydate%TYPE; + a_birthdate biography_data.birthdate%TYPE; + a_birthcomm biography_data.birthcomm%TYPE; + a_entrydate biography_data.entrydate%TYPE; + a_departdate biography_data.departdate%TYPE; BEGIN - SELECT biography_data.entrydate - INTO entrydate + -- Get the data of interest from BIOGRAPHY_DATA. + SELECT biography_data.birthdate, biography_data.birthcomm + , biography_data.entrydate, biography_data.departdate + INTO a_birthdate , a_birthcomm + , a_entrydate , a_departdate FROM biography_data - WHERE biography_data.animid = NEW.animid - AND NEW.startdate < biography_data.entrydate; - IF FOUND THEN + WHERE biography_data.animid = NEW.animid; + + -- An individual may not be placed in a community before birth. + IF NEW.startdate < a_birthdate then RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' , DETAIL = 'An individual may not be placed in a community' + || ' before birth' + || ': Key (CommMID) = (' + || NEW.commmid + || '), Value (AnimID) = (' + || NEW.animid + || '), Value (CommID) = (' + || NEW.commid + || '), Value (StartDate) = (' + || NEW.startdate + || '), Value (BIOGRAPHY_DATA.BirthDate) = (' + || a_birthdate + || ')'; + END IF; + + -- An individual may not be placed in a community, in which + -- they were not born, before the individual is under study. + IF NEW.commid IS DISTINCT FROM a_birthcomm + AND NEW.startdate < a_entrydate THEN + RAISE EXCEPTION integrity_constraint_violation USING + MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' + , DETAIL = 'An individual may not be placed in a community,' + || ' excepting their birth community,' || ' before the individual is under study' - || ': The new values in the row Key (CommMID) = (' + || ': Key (CommMID) = (' || NEW.commmid || '), Value (AnimID) = (' || NEW.animid @@ -151,30 +180,21 @@ CREATE OR REPLACE FUNCTION comm_membs_func () || NEW.commid || '), Value (StartDate) = (' || NEW.startdate - || ') place the individual in the community before' - || ' the individual came under study;' - || ' (BIOGRAPHY_DATA.EntryDate) = (' - || entrydate + || '), Value (BIOGRAPHY_DATA.BirthComm) = (' + || textualize(`a_birthcomm') + || '), Value (BIOGRAPHY_DATA.EntryDate) = (' + || a_entrydate || ')'; END IF; - END; - -- An individual may not be placed in a community after the - -- individual has left the study. - DECLARE - departdate biography_data.departdate%TYPE; - BEGIN - SELECT biography_data.departdate - INTO departdate - FROM biography_data - WHERE biography_data.animid = NEW.animid - AND biography_data.departdate < NEW.enddate; - IF FOUND THEN + -- An individual may not be placed in a community after the + -- individual has left the study. + IF a_departdate < NEW.enddate THEN RAISE EXCEPTION integrity_constraint_violation USING MESSAGE = 'Error on ' || TG_OP || ' of COMM_MEMBS' , DETAIL = 'An individual may not be placed in a community' || ' after the individual has left the study' - || ': The new values in the row Key (CommMID) = (' + || ': Key (CommMID) = (' || NEW.commmid || '), Value (AnimID) = (' || NEW.animid @@ -182,10 +202,8 @@ CREATE OR REPLACE FUNCTION comm_membs_func () || NEW.commid || '), Value (EndDate) = (' || NEW.enddate - || ') place the individual in the community after' - || ' the individual left the study;' - || ' Value (BIOGRAPHY_DATA.Departdate) = (' - || departdate + || '), Value (BIOGRAPHY_DATA.Departdate) = (' + || a_departdate || ')'; END IF; END; diff --git a/doc/src/tables/comm_membs.m4 b/doc/src/tables/comm_membs.m4 index 043d4d6..b49ea17 100644 --- a/doc/src/tables/comm_membs.m4 +++ b/doc/src/tables/comm_membs.m4 @@ -52,10 +52,22 @@ The |COMM_MEMBS.StartDate| of an individual with a given |COMM_MEMBS.CommID| may not be the day after the |COMM_MEMBS.EndDate| of a ``COMM_MEMBS`` row having the same |COMM_MEMBS.AnimID| value. -An individual may not be placed in a community unless that individual -is under study; the |COMM_MEMBS.StartDate| may not be before the -individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate| and the -|COMM_MEMBS.EndDate| may not be after the individual's + +An individual may not be a member of any community before their birth +date. +This means, the |COMM_MEMBS.StartDate| may not be before the +individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.BirthDate|. + +An individual may be a member of their birth community before they +became under study, but may not be a member of any other community +until they have come under study. +This means, when the |COMM_MEMBS.CommID| is not the |BIOGRAPHY_DATA|.\ +|BIOGRAPHY_DATA.BirthComm|, then the |COMM_MEMBS.StartDate| may not be +before the individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.EntryDate|. + +An individual may not be a member of a community after they cease to +be under study. +This means, the |COMM_MEMBS.EndDate| may not be after the individual's |BIOGRAPHY_DATA|.\ |BIOGRAPHY_DATA.DepartDate|. The |COMM_MEMBS.StartDate| must not be after the |COMM_MEMBS.EndDate|. -- 2.34.1